# -*- coding: utf-8 -*-
# @Time    : 2019/11/21 0021 下午 11:48
# @Author  : wulin
# @Email   : 286075568@qq.com
# @FileName: do_excel.py
# @Software: PyCharm


# excel的改造升级
import openpyxl
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
from openpyxl import *
import os, traceback
from copy import copy

from openpyxl.utils import get_column_letter

from common.outlog import MyLog

logger = MyLog('INFO')
class Reader:
    def __init__(self):
        # 整个excel工作簿缓存
        self.workbook = None
        self.wb = None
        # 当前工作sheet
        self.sheet = None
        self.rows = 0
        self.r = 0

    # 读取excel
    def open_ecxel(self, file_path):
        # 如果打开的文件不存在，就报错
        if not os.path.isfile(file_path):
            print("error：%s not exist!" % (file_path))
            return
        self.wb = load_workbook(file_path)

        sheets = self.wb.sheetnames

        self.sheet = sheets[0]
        # self.rows = self.get_sheets().max_row
        # 设置默认读取为第一行
        self.r = 0

        return sheets

    # 获取sheet页面
    def get_sheets(self):
        # 获取所有sheet的名字，并返回为一个列表
        # sheets = self.wb.get_sheet_names()
        sheets = self.wb.sheetnames
        # print(sheets)
        return sheets

    # 切换sheet页面
    def set_sheet(self, name):
        self.sheet = self.wb[name]
        self.rows = self.sheet.max_row

        self.r = 0
        return

    # 读取
    def readline(self, row):

        if self.r < self.rows:

            # 获取最大的列数
            columns = self.sheet.max_column
            rows = self.sheet.max_row
            rowdata = []
            # 循环遍历

            for i in range(1, columns + 1):
                cellvalue = self.sheet.cell(row=row + 1, column=i).value

                if cellvalue != None:
                    rowdata.append(cellvalue)
                else:
                    rowdata.append('')

            return rowdata


class Writer:

    def __init__(self):
        # 读取需要复制的excel
        self.workbook = None
        # 拷贝的工作空间
        self.wb = None
        # 当前工作的sheet页
        self.sheet = None
        # 记录生成的文件，用来保存
        self.df = None
        # 记录写入的行
        self.row = 0
        # 记录写入的列
        self.clo = 0

    # 复制并打开excel
    def copy_open(self, srcfile, dstfile):
        # 判断要复制的文件是否存在
        if not os.path.isfile(srcfile):
            print(srcfile + " not exist!")
            return

        # 判断要新建的文档是否存在，存在则提示
        if os.path.isfile(dstfile):
            # print("warning：" + dstfile + " file already exist!")
            logger.warning(dstfile + " file already exist!")

        # 记录要保存的文件
        self.df = dstfile
        self.workbook = load_workbook(filename=srcfile)
        # 拷贝
        self.wb = copy(self.workbook)

        return

    # 获取sheet页面
    def get_sheets(self):
        # 获取所有sheet的名字，并返回为一个列表
        sheets = self.wb.sheetnames

        logger.info(sheets)
        return sheets

    # 切换sheet页面
    def set_sheet(self, name):
        # 通过sheet名字，切换sheet页面
        # self.sheet = self.wb.get_sheet(name)
        self.sheet = self.wb[name]

        return

    # 写入指定单元格，保留原格式
    def write(self, r, c, value, color=None):

        """
        :param r: 行
        :param c: 列
        :param value: 要写入的字符串
        :param color: 0，黑色；1，白色；2，红色；3，绿色；4，蓝色；5，黄色
        :return:
        """

        # 获取要写入的单元格
        def _getCell(sheet, r, c):
            """ HACK: Extract the internal xlwt cell representation. """
            # 获取行
            row = sheet.rows
            if not row:
                return None
            # 获取单元格
            cell = sheet.columns
            return cell
            # 获取要写入的单元格

        cell = _getCell(self.sheet, r, c)
        # 格式，把单元格原来的格式保存下来
        # idx = cell.xf_idx

        if color is None:
            self.sheet.cell(r, c, value)
            # if cell:
            #     # 获取要写入的单元格
            #     ncell = _getCell(self.sheet, r, c)
            #     # if ncell:
            #     #     # 设置写入后格式和写入前一样
            #     #     ncell.xf_idx = idx

        else:

            """
            # Font：来设置文字的大小，颜色和下划线等
            # 
            # PatternFill：填充图案和渐变色
            # 
            # Border：单元格的边框
            # 
            # Alignment：单元格的对齐方式等
            """
            font = Font(name='Arial',
                        size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False,
                        color=color)
            # fill = PatternFill(fill_type=None, start_color='FFFFFFFF', end_color='FF000000')
            border = Border(left=Side(border_style='thin', color='000000'),
                            right=Side(border_style='thin', color='000000'),
                            top=Side(border_style='thin', color='000000'),
                            bottom=Side(border_style='thin', color='000000'),
                            diagonal=Side(border_style='thin', color='000000'),
                            diagonal_direction=0,
                            outline=Side(border_style='thin', color='000000'),
                            vertical=Side(border_style='thin', color='000000'),
                            horizontal=Side(border_style='thin', color='000000'))
            # alignment = Alignment(horizontal='general', vertical='bottom',
            #                       text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)
            number_format = 'General'
            protection = Protection(locked=True, hidden=False)

            mycesll = self.sheet.cell(r, c, value)
            mycesll.font = font
            # mycesll.fill = fill
            mycesll.border = border
            # mycesll.alignment = alignment

            return

    # 保存
    def save_close(self):
        # 保存复制后的文件到
        try:
            self.wb.save(self.df)
        except Exception as e:
            print("error：文件保存失败！")
            # print(traceback.format_exc(e))

        return


if __name__ == '__main__':
    alllist = []
    reader = Reader()
    reader.open_ecxel('../excel_case/Web.xlsx')
    sheetname = reader.get_sheets()
    print(sheetname)
    for sheet in sheetname:
     # 设置当前读取的sheet页面
        reader.set_sheet(sheet)
        # line = reader.readline(1)
        for i in range(reader.rows):
            line = reader.readline(i)
            print(line)


# writer = Writer()
# writer.copy_open('../excel_case/http_ydh.xlsx', '../test_result/result-http_ydh.xlsx')
# sheetname = writer.get_sheets()
# writer.set_sheet(sheetname[0])
# writer.write(2, 2, 'William', 'EE0000')
# # 00CD00绿色  #EE0000红色
# # writer.write(row=row + 1, 4).font = ft
#
# writer.save_close()
